package java6681.dao;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Junichiro
 * @description 数据表xslist的CRUD操作
 * @date 2021-05-23 13:30
 **/

public class Student6681Dao {
    public static void main(String[] args) {
//        testAdd6681();
        testFindAll6681();
    }

    public static void testAdd6681() {
        Student6681 student = new Student6681();
        student.setSno("add6681");
        student.setSname("小张");
        student.setLx1(70);
        student.setLx2(90);
//        int id = new Student6681Dao().add6681(student);
//        int id = new Student6681Dao().update6681(10,student);
        int id = new Student6681Dao().delete6681(8);
        System.out.println(id);
    }
    public static void testFindAll6681(){
        List<Student6681> list = new Student6681Dao().findAll6681();
        for (Student6681 student6681 : list) {
            System.out.println(student6681);
        }
    }


    public static int add6681(Student6681 student) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet keys = null;
        int ids = -1;
        try {
            conn = DBUtil6681.getConnection();
            String sql = "insert into xslist(sno,sname,lx1,lx2) values(?,?,?,?)";
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, student.getSno());
            pstmt.setString(2, student.getSname());
            pstmt.setInt(3, student.getLx1());
            pstmt.setInt(4, student.getLx2());
            pstmt.executeUpdate();
            keys = pstmt.getGeneratedKeys();
            if (keys.next()) {
                ids = keys.getInt(1);
                return ids;
            }
            return ids;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return ids;
        } finally {
            DBUtil6681.close(keys, pstmt, conn);
        }
    }

    public static int update6681(int id, Student6681 newStudent) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int count = -1;
        try {
            conn = DBUtil6681.getConnection();
            String sql = "update xslist set sno = ?,sname = ?,lx1 = ?,lx2 = ? where id = ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newStudent.getSno());
            pstmt.setString(2, newStudent.getSname());
            pstmt.setInt(3, newStudent.getLx1());
            pstmt.setInt(4, newStudent.getLx2());
            pstmt.setInt(5, id);
            count = pstmt.executeUpdate();
            if (count > 0) {
                return 1;
            } else {
                return -1;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return -1;
        } finally {
            DBUtil6681.close(pstmt, conn);
        }
    }

    public static int delete6681(int id) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        int count = -1;
        try {
            conn = DBUtil6681.getConnection();
            String sql = "delete from xslist where id = ? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            count = pstmt.executeUpdate();
            if (count > 0) {
                return 1;
            } else {
                return -1;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return -1;
        } finally {
            DBUtil6681.close(pstmt, conn);
        }
    }

    public static List<Student6681> findAll6681() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Student6681> stuList = null;
        try {
            conn = DBUtil6681.getConnection();
            stmt = conn.createStatement();
            String sql = "select * from xslist";
            rs = stmt.executeQuery(sql);
            stuList = new ArrayList<Student6681>();
            while (rs.next()) {
                int id = rs.getInt("id");
                String sno = rs.getString("sno");
                String sname = rs.getString("sname");
                int lx1 = rs.getInt("lx1");
                int lx2 = rs.getInt("lx2");

                Student6681 stu = new Student6681();
                stu.setId(id);
                stu.setSno(sno);
                stu.setSname(sname);
                stu.setLx1(lx1);
                stu.setLx2(lx2);

                stuList.add(stu);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtil6681.close(rs,stmt,conn);
        }
        return stuList;
    }
}
